package com.littlepay.kr4.paulin.dfeba77.fragment;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.preference.CheckBoxPreference;
import android.preference.EditTextPreference;
import android.preference.Preference;
import android.preference.PreferenceFragment;
import android.support.annotation.Nullable;

import com.littlepay.kr4.paulin.dfeba77.R;
import com.littlepay.kr4.paulin.dfeba77.application.MyApplication;
import com.littlepay.kr4.paulin.dfeba77.config.SqliteConfig;
import com.littlepay.kr4.paulin.dfeba77.utils.DBTransaction;
import com.littlepay.kr4.paulin.dfeba77.utils.DateUtil;
import com.littlepay.kr4.paulin.dfeba77.utils.PrefUtils;
import com.littlepay.kr4.paulin.dfeba77.utils.SqliteHelper;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//import com.orhanobut.logger.Logger;

/**
 * Created by john on 2017/5/15.
 */

public class UserSettingFragment extends PreferenceFragment {
    @Override
    public void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        final MyApplication application = (MyApplication) getActivity().getApplication();
        addPreferencesFromResource(R.xml.user_setting_preference);
       /* final EditTextPreference NickName = (EditTextPreference) getPreferenceManager()
                .findPreference(getString(R.string.rename));
        String s= NickName.getEditText().getText().toString();
        if(s!=null) {
            NickName.setSummary(s);
        }
        else
        {

        }*/
//        logoutButton = (Button) getActivity().findViewById(R.id.logout_button);
//        logoutButton.setOnClickListener(new View.OnClickListener() {
//            @Override
//            public void onClick(View view) {
//                PrefUtils.setLogin(false);
//                getActivity().finish();
//            }
//        });

        final CheckBoxPreference autoSync = (CheckBoxPreference) getPreferenceManager()
                .findPreference("auto_sync");
        autoSync.setOnPreferenceChangeListener(new Preference.OnPreferenceChangeListener() {

            /**
             * @param preference The changed Preference.
             * @param newValue   The new value of the Preference.
             * @return True to update the state of the Preference with the new value.
             */
            @Override
            public boolean onPreferenceChange(Preference preference, Object newValue) {

                boolean checked = Boolean.valueOf(newValue.toString());

                // PrefUtils.wifi_sync(true);
                //Logger.d("Pref " + preference.getKey() + " changed to " + newValue.toString());
                application.refresh();
                if (application.isWifiSync()) {
                    application.setWifiSync(false);
                } else {
                    application.setWifiSync(true);
                }
                //System.out.println(application.iswifi_sync());
                return true;

            }
        });


//        final Preference preference1=(Preference)getPreferenceManager()
//                .findPreference(getString(R.string.rename));
//        preference1.setOnPreferenceClickListener(new Preference.OnPreferenceClickListener()
//        {
//            @Override
//            public boolean onPreferenceClick(Preference preference) {
//
//
//                return true;
//            }
//        });
        final Preference editname = (Preference) getPreferenceManager().findPreference(getString(R.string.rename));
        editname.setOnPreferenceChangeListener(new Preference.OnPreferenceChangeListener() {
            @Override
            public boolean onPreferenceChange(Preference preference, Object newValue) {
                EditTextPreference editname = (EditTextPreference) getPreferenceManager().findPreference(getString(R.string.rename));
                String s = editname.getEditText().getText().toString();
                //String s= String.valueOf(application.getBudget());
                String format = null;
                format = editname.getSummary().toString();
                editname.setSummary(s);
                //application.setBudget(s);
                //System.out.println(application.getBudget());
                return true;
            }

        });

        new Thread(new Runnable() {
            @Override
            public void run() {
                //统一获取日期，让显示在recycleview和存入数据库的数据时间戳一致
                String date = DateUtil.getNowDate();
                PrefUtils prefUtils = new PrefUtils();
                //获取上次备份时间，lbut为last backup time
                String lbut = prefUtils.getBackUpTime();
                try {
                    downloadFromCloud();
                    checkDataBase();
                    if (lbut == "") {
                        synchronization();
                        prefUtils.setBackUpTime(date);
                    } else {
                        int interval = DateUtil.getIntervalDays(lbut);
                        if (interval > 3) {
                            synchronization();
                        }
                        prefUtils.setBackUpTime(date);
                    }
                }catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }).start();
    }


    //自动同步数据到数据库
    public void synchronization() throws SQLException {
        //从sharedpreference里头获取uid
        String uid = PrefUtils.getUid();
        //连接本地SQLite
        SqliteHelper sqliteHelper = new SqliteHelper(getActivity(), SqliteConfig.SQLITE_DATABASE_FILE_NAME, null, 1);
        //以只读方式打开SQLite
        SQLiteDatabase db = sqliteHelper.getReadableDatabase();
        //获得SQlite里头的各个属性的索引值
        Cursor cursor = db.query("stick", null, null, null, null, null, null);
        int idIndex = cursor.getColumnIndex("_id");
        int costIndex = cursor.getColumnIndex("cost");
        int attrIndex = cursor.getColumnIndex("attr");
        int detailIndex = cursor.getColumnIndex("detail");
        int dateIndex = cursor.getColumnIndex("date");
        //记录cursor的当前状态,当getPosition中的position没有值时候，返回0，即cursor的第一行
        int position = 0;
        //创建数据库连接
        DBTransaction transaction = DBTransaction.createInstance();
        transaction.connectDB();
        PreparedStatement ps = transaction.prepareStatement("INSERT INTO uid" + uid + " VALUES(?,?,?,?,?)");
        PreparedStatement ps1 =transaction.prepareStatement("SELECT max(id) FROM uid"+uid);
        transaction.setAutoCommit(false);
        while (!cursor.isAfterLast()) {
            //每次上传五十条数据，count用于控制每次只上传50条
            int count = 0;
            try {
                for (cursor.moveToPosition(position); (!(cursor.isAfterLast())) && count < 50; cursor.moveToNext()) {
                    ps.setInt(1,cursor.getInt(idIndex));
                    ps.setInt(2, cursor.getInt(costIndex) / 100);
                    ps.setString(3, cursor.getString(attrIndex));
                    ps.setString(4, cursor.getString(detailIndex));
                    ps.setString(5, cursor.getString(dateIndex));
                    ps.addBatch();
                    count++;
                }
                ps.executeBatch();
                transaction.commit();
            } catch (SQLException e) {
                transaction.rollback();
                e.printStackTrace();
            }
            //记录当前状态值
            position = cursor.getPosition();
        }
        ResultSet rs = ps1.executeQuery();
        while(!rs.next()){
            long CurrentId = Long.parseLong(rs.getString("id"));
            PrefUtils.setCurrentId(CurrentId);
        }
        cursor.close();
        transaction.closeDB();
    }

    public void downloadFromCloud() throws SQLException {
        //从sharedpreference里头获取uid
        String uid = PrefUtils.getUid();
        //从SharedPreference里头获取上次记录的id，CurrentId
        long CurrentId = PrefUtils.getCurrentId();
        //用来记录查询的最大的id
        long id = 0 ;
        //连接数据库从数据中获取最大的id
        DBTransaction transaction = DBTransaction.createInstance();
        transaction.connectDB();
        PreparedStatement preparedStatement = transaction.prepareStatement("SELECT max(id) FROM uid" + uid);
        ResultSet rs = preparedStatement.executeQuery();
        if(rs.next()){
            //新手机新用户中，本地数据库SQLite和云端都没有数据，将max（id）为空的时候，定义为0
            if(rs.getString("max(id)") == null){
                id = 0;
            }else {
                id = Long.parseLong(rs.getString("max(id)"));
            }
        }
        if(id > CurrentId){
            preparedStatement = transaction.prepareStatement("SELECT * FROM uid"+ uid+" WHERE id>"+CurrentId);
            ResultSet resultSet = preparedStatement.executeQuery();
            SqliteHelper sqliteHelper = new SqliteHelper(getActivity(), SqliteConfig.SQLITE_DATABASE_FILE_NAME, null, 1);
            int iid;
            int iCost;
            int iAtrribute;
            String idetail;
            String idate;
            while(resultSet.next()){
                iid = resultSet.getInt("id");
                iCost = resultSet.getInt("cost");
                iAtrribute = resultSet.getInt("attribution");
                idetail = resultSet.getString("detail");
                idate =resultSet.getString("date");
                sqliteHelper.insertDataFromCloud(iid,iCost,iAtrribute,idetail,idate);
            }
        }
        transaction.closeDB();
    }


    //检查数据库中的数据是否与本数据库中的数据完全一致
    public void checkDataBase(){
        DBTransaction transaction = DBTransaction.createInstance();
        transaction.connectDB();
        String uid = PrefUtils.getUid();
        PreparedStatement ps = transaction.prepareStatement("SELECT * FROM uid"+uid);
        PreparedStatement ps1 = transaction.prepareStatement("DELETE FROM uid"+uid+" WHERE id=?");
        try {
            ResultSet resultSet = ps.executeQuery();
            int check = 0;
            while(resultSet.next()){
                check++;
            }
            //连接本地SQLite
            SqliteHelper sqliteHelper = new SqliteHelper(getActivity(), SqliteConfig.SQLITE_DATABASE_FILE_NAME, null, 1);
            int num = sqliteHelper.numOfDataBase();
            if(check > num){
                //以只读的方式打开数据库
                SQLiteDatabase db = sqliteHelper.getReadableDatabase();
                //查询SQLite中所有的数据
                Cursor cursor = db.query("stick", null, null, null, null, null, "_id desc");
                //以降序排列，最大值在最顶端，故先移动到第一个
                cursor.moveToFirst();
                //获取id的索引值
                int idIndex = cursor.getColumnIndex("_id");
                //检查与删除所用id，其初值为最大id号
                int id = cursor.getInt(idIndex);
                //循环查询SQLite数据库中被删除的id
                for (cursor.moveToFirst();(!cursor.isAfterLast());cursor.moveToNext()){
                    //比较id号，查询被删除掉的id号
                    //因为需要在id号相等的时候，让maxid自减，删除多条的时候让它找到原本的id号，因此即需要while也需要if
                    if(cursor.getInt(idIndex) != id) {
                        while (cursor.getInt(idIndex) != id) {
                            //将要被删除的id号添加到preparedstatement里头
                            ps1.setInt(1, id);
                            //对其进行批处理
                            ps1.addBatch();
                            id--;
                        }
                        id--;
                    }else{
                        id--;
                    }
                }
                //关闭光标
                cursor.close();
                //关闭SQLite数据库
                db.close();
                //将删除语句提交
                ps1.executeBatch();
                //关闭云端数据库
                transaction.closeDB();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

